package dao;

import java.sql.SQLException;
import java.util.List;


import domain.Notice;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.DataSourceUtils;


public class NoticeDao {
	//后台系统，查询所有的公告
	public List<Notice> getAllNotices() throws SQLException {
		String sql = "select * from notice order by n_time desc limit 0,10";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new BeanListHandler<Notice>(Notice.class));
	}

	//分页查询，每次查询只返回一页的记录，参数两个，页的大小page_size,页的序号page_index,序号从一开始，记录下标从零开始
	public List<Notice> getPage(int page_size,int page_index) throws  SQLException{
		String sql = "select * from notice order by n_time desc limit ?,?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new BeanListHandler<Notice>(Notice.class),((page_index-1)*page_size),page_size);
	}

	//后台系统，添加公告,如果添加成功，返回为真，失败返回为假
	public boolean addNotice(Notice n) throws SQLException {
		String sql = "insert into notice(title,details,n_time) values(?,?,?)";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		int num=runner.update(sql, new Object[]{n.getTitle(),n.getDetails(),n.getN_time()});
		if (num>0) {
			return true;
		}
		return false;
	}

	//后台系统，根据id查找公告
	public Notice findNoticeById(String n_id) throws SQLException {
		String sql = "select * from notice where n_id = ?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new BeanHandler<Notice>(Notice.class),n_id);
	}

	//后台系统，根据id修改单个公告，如果修改成功，返回为真，失败返回为假
	public boolean updateNotice(Notice n) throws SQLException {
		String sql = "update notice set title=?,details=?,n_time=? where n_id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		int num=runner.update(sql, n.getTitle(),n.getDetails(),n.getN_time(),n.getN_id());
		if (num>0) {
			return true;
		}
		return false;
	}

	//后台系统，根据id删除公告，如果删除成功，返回为真，失败返回为假
	public boolean deleteNotice(String n_id) throws SQLException {
		String sql = "delete from notice where n_id = ?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		int num=runner.update(sql, n_id);
		if (num>0) {
			return true;
		}
		return false;
	}

	//前台系统，查询最新添加或修改的一条公告
	public Notice getRecentNotice() throws SQLException {
		String sql = "select * from notice order by n_time desc limit 0,1";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new BeanHandler<Notice>(Notice.class));
	}
}
